# _*_ coding:utf-8 _*_
# @Time  : 2023.09.05
# @Author: zizlee
from typing import List
from fastapi import APIRouter, Query, Body, Depends
from fastapi.encoders import jsonable_encoder
from pydantic import BaseModel
from utils.jwt_token import TokenPayload
from utils.module_public import ModulePublic
from response import APIResponse
from constant.variety import VarietyConstants
from constant.section_scope import SectionScope
from db_utils import BaseDatabaseConnection


class VarietyAuth:
    def user_variety(self, payload: TokenPayload):
        public_util = ModulePublic(module_key=SectionScope.CALCULATE_PLAT_VARIETY_SCOPE, module_sec=SectionScope.CALCULATE_PLAT_KEY)
        variety_list = public_util.access_variety(payload=payload)

        # utp = payload.user_type()
        # if utp == UserType.REGISTER_USER:
        #     return APIResponse.success(data={'variety': []})  # 全无
        # # 全有
        # a_sql = """
        #     SELECT id,`code`,`name`,group_id FROM ruizhi_variety WHERE is_active=1 AND FIND_IN_SET('S06',scope) ORDER BY sort_num;
        # """
        # a_prm = None
        #
        # # 看权限
        # b_sql = """
        #     SELECT * FROM ruizhi_user_section_variety As a
        #     INNER JOIN ruizhi_variety As b ON a.variety_en=b.code
        #     WHERE a.sec_key='SEC006' AND a.active_expire>=CURDATE() AND a.user_id=%s;
        # """
        #
        # b_prm = [payload.user_id()]
        #
        # sql = a_sql
        # prm = a_prm
        # with BaseDatabaseConnection() as conn:
        #     if utp in UserType.VARIETY_AUTH_SETS:
        #         # 查询用户是不是全品种
        #         conn.cursor.execute("SELECT id FROM ruizhi_user_section_variety WHERE sec_key=%s AND variety_en='VAR_ALL' AND user_id=%s AND expire_date>=CURDATE() LIMIT 1;",
        #                             [SectionScope.CALCULATE_PLAT_KEY, payload.user_id()])
        #         all_access = conn.cursor.fetchone()
        #         if not all_access:
        #             sql = b_sql
        #             prm = b_prm
        #     conn.cursor.execute(sql, prm)
        #     variety_list = conn.cursor.fetchall()

        for v in variety_list:
            v['is_real'] = True
            v['name'] = f'{v["name"]} ({v["code"]})'
        if len(variety_list) > 10:
            group_variety = []
            for g, gname in VarietyConstants.GROUPS.items():
                g_v_obj = {
                    'name': gname,
                    'code': g,
                    'is_real': False,
                    'children': []
                }
                if g in VarietyConstants.INACTIVE_GROUPS:
                    continue
                for v in variety_list:
                    if v['group_id'] == g:
                        g_v_obj['children'].append(v)
                if len(g_v_obj['children']) > 0:
                    group_variety.append(g_v_obj)
            variety_list = group_variety
        return APIResponse.success(data={'variety': variety_list})


class FormulaColumnItem(BaseModel):
    cal_value: float
    ID: int


class ReCalculateItem(BaseModel):
    fid: int
    columns: List[FormulaColumnItem]


class Formula:

    def __init__(self, group_id: int = None, ven: str = None):
        self.gid = group_id  # 组别ID
        self.ven = ven  # 品种代码

    def calculate_formula_result(self, formula):
        column_values = {f'ID_{col["ID"]}': col['cal_value'] for col in formula['columns']}
        expression = formula['formula']
        for k, v in column_values.items():
            expression = expression.replace(k, str(v))
        try:
            result = round(eval(expression), 4)
        except ValueError:
            result = None
        except TypeError:
            result = None
        return result

    # 获取组下公式和计算项
    def get_group_formula_list(self):
        sql = """
            SELECT f.id,f.group_id,f.name,f.formula,f.vcode,f.sort_num,f.unit,
                c.id As ID,c.formula_id,c.name As cname,c.is_constant,c.constant,c.is_index,c.index_id,c.unit As cunit
            FROM calcul_group_formula As f
            RIGHT JOIN calcul_formula_column As c
            ON f.id=c.formula_id
            WHERE f.is_active=1 AND f.group_id=%s
            ORDER BY f.sort_num,c.sort_num;
        """
        with BaseDatabaseConnection() as conn:
            conn.cursor.execute(sql, [self.gid])
            ret_list = list(conn.cursor.fetchall())

            tmp_dict = {}
            for r in ret_list:
                col = {k2: r[k2] for k2 in ['ID', 'cname', 'is_constant', 'constant', 'is_index', 'index_id', 'cunit']}

                col['cal_value'] = r['constant']
                col['unit'] = r['cunit']
                if col['is_index'] and col['index_id']:
                    # 查询指标数据的最新值
                    conn.cursor.execute("SELECT end_value,unit FROM datlib_index WHERE id=%s;", [col['index_id']])
                    value = conn.cursor.fetchone()
                    if value:
                        col['cal_value'] = value['end_value']
                        col['unit'] = value['unit']

                if r['id'] in tmp_dict:
                    _obj = tmp_dict[r['id']]
                    _obj['columns'].append(col)
                else:
                    tmp_dict[r['id']] = {
                        **{k: r[k] for k in ['id', 'group_id', 'name', 'formula', 'unit','vcode']},
                        'columns': [col],
                    }
        formula_list = list(tmp_dict.values())
        for f in formula_list:
            f['result'] = self.calculate_formula_result(formula=f)
            del f['formula']
            # print({k: f[k] for k in ['id', 'group_id', 'name', 'formula', 'vcode', 'result']})
            # # 计算结果值
            # for c in f['columns']:
            #     print('\t', c)
        return APIResponse.success({'data': formula_list})

    # 获取品种下的公式和计算项
    def get_variety_formula_list(self):
        if not self.ven:
            return APIResponse.success({'data': []})
        sql = """
            SELECT f.id,f.group_id,f.name,f.formula,f.vcode,f.sort_num,f.unit,
                c.id As ID,c.formula_id,c.name As cname,c.is_constant,c.constant,c.is_index,c.index_id,c.unit As cunit,c.is_show
            FROM calcul_group_formula As f
            RIGHT JOIN calcul_formula_column As c
            ON f.id=c.formula_id
            WHERE f.is_active=1 AND f.vcode=%s
            ORDER BY f.sort_num,c.sort_num;
        """
        with BaseDatabaseConnection() as conn:
            conn.cursor.execute(sql, [self.ven])
            ret_list = list(conn.cursor.fetchall())
            tmp_dict = {}
            for r in ret_list:
                col = {k2: r[k2] for k2 in ['ID', 'cname', 'is_constant', 'constant', 'is_index', 'index_id', 'cunit', 'is_show']}

                col['cal_value'] = r['constant']
                col['unit'] = r['cunit']
                if col['is_index'] and col['index_id']:
                    # 查询指标数据的最新值
                    conn.cursor.execute("SELECT end_value,unit FROM datlib_index WHERE id=%s;", [col['index_id']])
                    value = conn.cursor.fetchone()
                    if value:
                        col['cal_value'] = value['end_value']
                        col['unit'] = value['unit']

                if r['id'] in tmp_dict:
                    _obj = tmp_dict[r['id']]
                    _obj['columns'].append(col)
                else:
                    tmp_dict[r['id']] = {
                        **{k: r[k] for k in ['id', 'group_id', 'name', 'formula', 'unit','vcode']},
                        'columns': [col],
                    }
        formula_list = list(tmp_dict.values())
        for f in formula_list:
            f['result'] = self.calculate_formula_result(formula=f)
            del f['formula']
            # print({k: f[k] for k in ['id', 'group_id', 'name', 'formula', 'vcode', 'result']})
            # # 计算结果值
            # for c in f['columns']:
            #     print('\t', c)
        return APIResponse.success({'data': formula_list})

    # 公式数据重新计算
    def re_calculate_formula(self, calculation: ReCalculateItem):
        with BaseDatabaseConnection() as conn:
            conn.cursor.execute("SELECT name,formula FROM calcul_group_formula WHERE id=%s LIMIT 1;",
                                [calculation.fid])
            formula = conn.cursor.fetchone()
        if not formula:
            return APIResponse.validate_error(detail='公式不存在，无法计算!')
        formula['columns'] = jsonable_encoder(calculation.columns)
        result = self.calculate_formula_result(formula=formula)
        return APIResponse.success(data={'result': result, 'name': formula['name'], 'fid': calculation.fid})


formula_api = APIRouter()


@formula_api.get('/variety/')  # 获取有权限的品种列表
def user_variety(payload: TokenPayload = Depends(TokenPayload)):
    return VarietyAuth().user_variety(payload=payload)


@formula_api.get('/varietyFormulaList/')  # 获取品种下的公式列表
def variety_formula_list(ven: str = Query(...)):
    return Formula(ven=ven).get_variety_formula_list()


@formula_api.post('/reCalculate/')  # 重新计算
def re_calculate(calculate_item: ReCalculateItem = Body(...)):
    return Formula().re_calculate_formula(calculation=calculate_item)


# @formula_api.get('/groupFormulaList/')  # 获取组下的公式列表
def group_formula_list(group: int = Query(...)):  # (暂关闭，公式没有做分组)
    return Formula(group_id=group).get_group_formula_list()



